set spark.sql.crossJoin.enabled=true;
with retention_summary as (
    select final_sign_network_code                       --最终签收派件网点编码
         , end_agent_code                                --末端网点所属代理区编码
         , final_sign_network_name                       --最终签收派件网点编码
         , end_agent_name                                --末端网点所属代理区名称
         , end_provider_id                               --末端网点所属省份id
         , end_provider_name                             --末端网点所属省份名称
         , end_city_id                                   --末端网点所属城市id
         , end_city_name                                 --末端网点所属城市名称
         , sum(need_sign_count_1d) as need_sign_count_1d --1天应签收
         , sum(retention_count_1d) as retention_count_1d --1天内滞留
         , sum(need_sign_count_2d) as need_sign_count_2d --2天应签收
         , sum(retention_count_2d) as retention_count_2d --2天内滞留
         , sum(need_sign_count_3d) as need_sign_count_3d --3天应签收
         , sum(retention_count_3d) as retention_count_3d --3天内滞留
         , sum(need_sign_count_5d) as need_sign_count_5d --5天应签收
         , sum(retention_count_5d) as retention_count_5d --5天内滞留
         , sum(need_sign_count_7d) as need_sign_count_7d --7天应签收
         , sum(retention_count_7d) as retention_count_7d --7天内滞留
         , final_plan_sign_date                          --规划签收日期
    from jms_dm.dm_network_retention_summary_dt --网点滞留件汇总新
    where dt = '{{ execution_date | cst_ds }}'
      and is_contain_difficult = 2
    group by final_sign_network_code --最终签收派件网点编码
           , end_agent_code          --末端网点所属代理区编码
           , final_sign_network_name --最终签收派件网点编码
           , end_agent_name          --末端网点所属代理区名称
           , final_plan_sign_date
           , end_provider_id         --末端网点所属省份id
           , end_provider_name       --末端网点所属省份名称
           , end_city_id             --末端网点所属城市id
           , end_city_name --末端网点所属城市名称
),
     taking_union as (
         select network_code
              , network_name
              , sum(scan_sum) as scan_sum
              , type1
              , date_time
         from jms_dm.dm_network_taking_union_sign_hour_sum_dt --揽签分布汇总表
         where dt = '{{ execution_date | cst_ds }}'
           and type1 = '时效签收量'
         group by network_code
                , network_name
                , type1
                , date_time
     ),
     summary_plan_sign as (
         select final_sign_network_code
              , split(deliver_shift, ',')[0] as deliver_shift1
              , split(deliver_shift, ',')[1] as deliver_shift2
              , need_sign_count
         from (
                  select final_sign_network_code
                       , concat_ws(',', sort_array(collect_set(cast(deliver_shift as string)))) as deliver_shift
                       , max(need_sign_count)                                                   as need_sign_count
                  from (
                           select final_sign_network_code
                                , deliver_shift
                                , if(deliver_shift = 0, need_sign_count, 0) as need_sign_count
                           from (
                                    select final_sign_network_code                                                                      --网点
                                         , deliver_shift
                                         , sum(
                                            if(need_sign_count = '' or need_sign_count is null, 0, need_sign_count)) as need_sign_count --应签收量
                                    from jms_dm.dm_terminal_sign_summary_plan_sign_new_dt --新末端签收汇总表(规划签收)
                                    where dt = '{{ execution_date | cst_ds }}' --分区是规划签收时间
--            and deliver_shift = 0
                                    group by final_sign_network_code, deliver_shift
                                ) a
                       ) a
                  group by final_sign_network_code
              ) a
     ),
     warm_config_before as (
         select datetime
              , max(1pressure) as 1pressure
              , max(3pressure) as 3pressure
              , max(5pressure) as 5pressure
              , max(7pressure) as 7pressure
              , max(1frame)    as 1frame
              , max(3frame)    as 3frame
              , max(5frame)    as 5frame
              , max(7frame)    as 7frame
         from (
                  select datetime
                       , if(time_frame = '1日内', pressure, 0)    as 1pressure --压力值
                       , if(time_frame = '3日内', pressure, 0)    as 3pressure
                       , if(time_frame = '5日内', pressure, 0)    as 5pressure
                       , if(time_frame = '7日内', pressure, 0)    as 7pressure
                       , if(time_frame = '1日内', count_frame, 0) as 1frame    --票量
                       , if(time_frame = '3日内', count_frame, 0) as 3frame
                       , if(time_frame = '5日内', count_frame, 0) as 5frame
                       , if(time_frame = '7日内', count_frame, 0) as 7frame
                  from (
                           select time_frame
                                , pressure
                                , count_frame
                                , effective_start_time
                                , effective_end_time
                                , '{{ execution_date | cst_ds }}' as datetime
                           from jms_dim.dim_tab_warehouse_warm_config_base
                           where delete_flag = 1
                             and substr(effective_start_time,1,10) <= '{{ execution_date | cst_ds }}'
                             and substr(effective_end_time,1,10) >= '{{ execution_date | cst_ds }}'
                       ) tmp
              ) t
         group by datetime
     ),
     network_whole as (--取虚拟代理区
         select code
              , name
              , virt_code
              , virt_name
              , zone_id
              , zone_code
              , zone_name
              , area_code
              , area_name
         from jms_dim.dim_network_whole_massage
         where network_type = 6
     ),

     leader_config as (--去配置代理区的管理员
         select agent_code
              , staff_code    --员工编码
              , staff_name    --员工名称
              , position_name --岗位名称
         from jms_dim.dim_tab_agent_leader_config_base
         group by agent_code
                , staff_code --员工编码
                , staff_name --员工名称
                , position_name --岗位名称

     ),
     result_table_mid as (
         select t1.final_sign_network_code            --最终签收派件网点编码
              , t1.final_sign_network_name            --最终签收派件网点编码
--               , t1.end_agent_code                     --末端网点所属代理区编码
--               , t1.end_agent_name                     --末端网点所属代理区名称
              , t1.end_agent_code                     --末端网点所属代理区编码
              , t1.end_agent_name                     --末端网点所属代理区名称
              , t1.end_provider_id                    --末端网点所属省份id
              , t1.end_provider_name                  --末端网点所属省份名称
              , t1.need_sign_count_1d                 --1天应签收
              , t1.retention_count_1d                 --1天内滞留
              , t1.need_sign_count_2d                 --2天应签收
              , t1.retention_count_2d                 --2天内滞留
              , t1.need_sign_count_3d                 --3天应签收
              , t1.retention_count_3d                 --3天内滞留
              , t1.need_sign_count_5d                 --5天应签收
              , t1.retention_count_5d                 --5天内滞留
              , t1.need_sign_count_7d                 --7天应签收
              , t1.retention_count_7d                 --7天内滞留
              , t2.scan_sum                           --揽签分布汇总表 时效签收量
              , t1.final_plan_sign_date               --规划签收日期
              , t3.need_sign_count as need_sign_count --新末端签收汇总表(规划签收) 时效应签收量
              , t1.end_city_id                        --末端网点所属城市id
              , t1.end_city_name                      --末端网点所属城市名称
              , t3.deliver_shift1
              , t3.deliver_shift2
         from retention_summary t1
                  left join taking_union t2
                            on t1.final_sign_network_code = t2.network_code
                                and t1.final_plan_sign_date = t2.date_time
                  left join summary_plan_sign t3
                            on t1.final_sign_network_code = t3.final_sign_network_code
     )

insert
overwrite
table
jms_dm.dm_mng_explosion_warehouse_network_punish_mid_dt
partition
(
dt
)
select t1.final_sign_network_code                                                   --派件网点编码
     , t1.final_sign_network_name                                                   --派件网点名称
     , t1.end_agent_code                                                            --所属代理区编码
     , t1.end_agent_name                                                            --所属代理区名称
     , t1.end_provider_id                                                           --所属省份id
     , t1.end_provider_name                                                         --所属省份名称
     , t1.need_sign_count_1d                                                        --1天应签收
     , t1.retention_count_1d                                                        --1天内滞留
     , t1.need_sign_count_2d                                                        --2天应签收
     , t1.retention_count_2d                                                        --2天内滞留
     , t1.need_sign_count_3d                                                        --3天应签收
     , t1.retention_count_3d                                                        --3天内滞留
     , t1.need_sign_count_5d                                                        --5天应签收
     , t1.retention_count_5d                                                        --5天内滞留
     , t1.need_sign_count_7d                                                        --7天应签收
     , t1.retention_count_7d                                                        --7天内滞留
     , t1.scan_sum                                                                  --时效签收量揽签分布汇总表
     , t1.final_plan_sign_date                                                      --规划签收日期
     , t1.need_sign_count                                                           --零班票量  时效应签收量新末端签收汇总表(规划签收)
     , t2.staff_code                                                                --员工编码
     , t2.staff_name                                                                --员工名称
     , t2.position_name                                                             --岗位名称.
     , t3.virt_code                                                                 --虚拟代理区code
     , t3.virt_name                                                                 --虚拟代理区name
     , t4.3pressure                                                                 --3日压力值
     , t4.3frame                                                                    --3日票量
     , t4.5pressure                                                                 --5日压力值
     , t4.5frame                                                                    --5日票量
     , t4.7pressure                                                                 --7日压力值
     , t4.7frame                                                                    --7日票量
     , case
           when (t1.retention_count_3d / nvl(t1.scan_sum, 1) >= t4.3pressure and retention_count_3d >= t4.3frame) or
                (t1.retention_count_5d / nvl(t1.scan_sum, 1) >= t4.5pressure and retention_count_5d >= t4.5frame) or
                (t1.retention_count_7d / nvl(t1.scan_sum, 1) >= t4.7pressure and retention_count_7d >= t4.7frame)
               then 1
           else 0 end                                                  as yali_flag --1代表压力值满足的要保留,0非代表不满足任何一种情况
     , if(t1.deliver_shift1 = 0 and t1.deliver_shift2 is not null and
          nvl(t1.need_sign_count, 0) > retention_count_1d * 0.5, 1, 0) as zero_flag --1代表要剔除 应签收量>1日滞留量*50%  0要保留
     , t1.end_city_id                                                               --末端网点所属城市id
     , t1.end_city_name                                                             --末端网点所属城市名称
     , t3.zone_code                                                    as district_code
     , t3.zone_name                                                    as district_desc
     , t3.zone_id                                                      as district_id
     , t3.area_code
     , t3.area_name
     ,if(t1.retention_count_3d / nvl(t1.scan_sum, 1) >= t4.3pressure and retention_count_3d >= t4.3frame,1,0) as three_pre_flag     --3日判断1是
     ,if(t1.retention_count_5d / nvl(t1.scan_sum, 1) >= t4.5pressure and retention_count_5d >= t4.5frame,1,0) as five_pre_flag      --5日判断1是
     ,if(t1.retention_count_7d / nvl(t1.scan_sum, 1) >= t4.7pressure and retention_count_7d >= t4.7frame,1,0) as seven_pre_flag     --7日判断1是
     , '{{ execution_date | cst_ds }}'                                 as dt
from result_table_mid t1
         left join leader_config t2
                   on t1.end_agent_code = t2.agent_code
         left join network_whole t3
                   on t1.final_sign_network_code = t3.code
         left join warm_config_before t4
                   on t1.final_plan_sign_date = t4.datetime
    distribute by dt;




